import math

from sqlalchemy import create_engine, MetaData, Table, text, column
from sqlalchemy.orm import sessionmaker

#
engine = create_engine('mysql+pymysql://root:iagnjG3SfC160DQA@47.105.55.191:3306/population?charset=utf8mb4')
metadata = MetaData()

# 建立会话
Session = sessionmaker(bind=engine)
session = Session()




class Population:
    Dic = {}

    # 获取年份和分类
    def year_divide(self,table_name):
        # 反射特定表
        table = Table(table_name, metadata, autoload_with=engine)

        # 获取年份
        column_names = [column.name for column in table.columns]
        year = column_names[1:]
        column_name = column_names[0]

        # 获取分类

        divide = session.query(table.c[column_name]).all()
        divide1 = []
        for i in divide:
            divide1.append(i[0])

        return year,divide1,column_name,table

    # 获取一行
    def item_fetch(self,table,divide_name,divide):
        with engine.connect() as connection:
            #   按省份获取一整行数据
            query = table.select().where(table.c[divide_name] == divide)
            result = connection.execute(query)
            item = result.fetchone() # 一行数据

        return item

    # 获取一列
    def column_fetch(self,table,column_name):
        re = []
        for i in session.query(table.c[column_name]).all():
            re.append(i[0])
        return re  # 一列

    # 查询整张表
    def table_fetch(self,table_name):
        # 反射表结构
        metadata = MetaData()
        table = Table(table_name, metadata, autoload_with=engine)

        # 查询整张表
        query = table.select()
        result_proxy = session.execute(query)

        # 获取所有结果
        results = result_proxy.fetchall()

        return results

    # 按年查询数据

    def staticdata(self, table_name):
        dic = {}

        # 获取年份和分类
        year,divide,column_name,table = self.year_divide(table_name)

        # result = session.execute(Table(table_name, metadata, autoload_with=engine).select())
        #
        # results = result.fetchall()
        # for row in results:
        #     print(row)

        for i in year:
            # item = session.query(table.c[i]).all()      #一列
            item = self.column_fetch(table,i)     #一列
            count = 0
            li = []  # 存储每个省数据（字典）的列表
            # print(item)
            for j in divide:
                value = item[count]
                if (value == None):
                    value = 0
                li.append({j: float(value)})  # 加入一年各个数据的字典
                count += 1
            dic[int(i[:-1])] = li
            # print(li)
        return dic

    # 按省查询

    def staticprovince(self, table_name):
        dic = {}

        # # 反射特定表
        # table = Table(table_name, metadata, autoload_with=engine)

        # 获取年份和分类
        year, divide, column_name,table = self.year_divide(table_name)

        for i in divide:
            i = divide
            # item = session.query(i)
            item = self.item_fetch(table,column_name,i) # 一行数据
            count = 0
            li = []  # 存储每个省数据（字典）的列表

            for j in year:
                li.append({j: item[table.c[j]]})  # 加入一年各个数据的字典
                count += 1
            dic[i] = li
        return dic

    def age(self):
        age = "age_structure"
        self.Dic["age"] = self.staticdata(age)

    # 男性
    def gendermale(self):
        gender = "boys_year_data"
        self.Dic["male"] = self.staticdata(gender)
        # print(self.staticdata(gender))

    # 女性
    def genderfemale(self):
        gender = "girls_year_data"
        self.Dic["female"] = self.staticdata(gender)


    # 男女占比
    def rategender(self):
        rategender = "sex_structure"
        self.Dic["gender"] = self.staticdata(rategender)

    # 家庭结构
    def family(self):
        # 全国家庭结构
        all_data = {}  # 存放所有数据的字典
        family = "household_structure_2"
        # table1 = Table(family, metadata, autoload_with=engine)
        # 获取年份和分类
        year, divide, column_name, table1 = self.year_divide(family)
        china_dic = {}  # 存放全国年数据的字典
        country_data = self.table_fetch(family)
        count_divide = 1
        for i in year:
            count = 0
            year_data = []  # 一年的数据
            for j in divide:
                year_data.append({j: float(country_data[count][count_divide])})     # 每个类别的数据
                count += 1
            china_dic[int(i[:-1])] = year_data  # 加入一年的数据
            count_divide += 1
        all_data["china"] = china_dic

        #  分省家庭结构
        table_name = ["one_household_structure",
                    "two_household_structure",
                    "three_household_structure",
                    "four_household_structure",
                    "five_household_structure",
                    "six_household_structure",
                    "seven_household_structure",
                    "eight_household_structure",
                    "nine_household_structure",
                    "ten_or_more_household_structure"]

        province_data = []
        for i in table_name:
            province_data.append(self.table_fetch(i))


        year1, divide1, column_name1,table = self.year_divide(table_name[0])
        divide_count = 0
        for x in divide1:
            city_dic = {}  # 存放每个省年数据的字典
            count = 1
            for i in year1:
                year_data = []  # 一年的数据
                table_count = 0
                for j in range(len(table_name)):
                    value = province_data[j][divide_count][count]
                    if value == None:
                        value = 0
                    year_data.append({divide[table_count]: float(value)})
                    table_count += 1
                count += 1
                city_dic[int(i[:-1])] = year_data  # 加入一年的数据
            all_data[x] = city_dic  # 单个城市所有年份数据加入
            divide_count += 1
        self.Dic["family_new"] = all_data

    # 变化数据
    def change(self):
        table_name = ["province_birth_rate","province_death_rate","province_growth_rate","year_change_data"]
        data = {}
        # 获取年份和分类
        year, divide, column_name, table_birth = self.year_divide(table_name[0])
        # year, divide, column_name, table_die = self.year_divide(table_name[1])
        # year, divide, column_name, table_ascent = self.year_divide(table_name[2])
        birth = self.table_fetch(table_name[0])
        die = self.table_fetch(table_name[1])
        ascent = self.table_fetch(table_name[2])
        count_divide = 0
        for i in divide:
            # 每个城市所有年的各个改变数据
            item_birth = birth[count_divide]
            item_die = die[count_divide]
            item_ascent = ascent[count_divide]
            count_divide += 1
            # 单个城市所有年的改变数据
            li = []  # 单个城市所有年的数据
            count = 1
            for j in year:
                value1 = item_birth[count]
                value2 = item_die[count]
                value3 = item_ascent[count]
                if (value1 == None):
                    value1 = 0
                if (value2 == None):
                    value2 = 0
                if (value3 == None):
                    value3 = 0
                li.append({j:[float(value1),float(value2),float(value3)]})
                count += 1
            data[i] = li


        # 全国
        year, divide, column_name, table = self.year_divide(table_name[3])
        country_data = self.table_fetch(table_name[3])
        li = []  # 全国所有年的数据
        count_year = 0
        # print(year)
        # print(country_data)
        for i in year:
            count_year += 1
            count = 0
            li_year = []
            for j in divide:
                if(count_year==0):
                    continue
                # print(count)
                # print(count_year)
                value = country_data[count][count_year]
                if(value == None):
                    value = 0
                li_year.append({j:float(value)})
                count += 1
            li.append({i:li_year})
        data["china"] = li
        self.Dic["change"] = data


    # 全国总人口
    def population(self):
        table_name = "year_data"
        # 获取年份和分类
        year, divide, column_name, table = self.year_divide(table_name)

        population = self.item_fetch(table,column_name,divide[0])

        li = []
        count = 1
        for i in year:
            li.append({i:population[count]})
            count +=1
        self.Dic["population"] = li

    # 2023年各个省人口
    def eachpopulation(self):
        table_name = "province_permanent_resident_population"
        # 获取年份和分类
        year, divide, column_name, table = self.year_divide(table_name)

        population = self.column_fetch(table,"2023年")

        li = []
        count = 0
        for i in divide:
            li.append({i:float(population[count])})
            count += 1
        self.Dic["lastyear"] = li



    # 查询
    def getdata(self):
        self.age()
        self.change()
        self.family()
        self.genderfemale()
        self.gendermale()
        self.rategender()
        self.population()
        self.eachpopulation()


p = Population ()
p.getdata()
# print(p.Dic["change"]["china"])
# print(p.Dic["gender"][2023])
# print(p.Dic["family"]["北京市"])
# # print(p.Dic["lastyear"])
# print(p.Dic["change"]["北京市"])
# print(p.Dic["male"][2021])
# print(p.Dic["male"][2018])
# print(p.Dic["age"])


